from datetime import datetime, timedelta
from app import get_db_connection  # 从app.py导入数据库连接函数

# ------------------------------
# 数据库表结构定义 (SQL语句)
# ------------------------------
TABLE_DEFINITIONS = {
    # 用户表
    'users': """
    CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(64) NOT NULL UNIQUE,
        email VARCHAR(120) NOT NULL UNIQUE,
        password_hash VARCHAR(128) NOT NULL,
        phone VARCHAR(20) NULL,
        role VARCHAR(20) NOT NULL DEFAULT 'user',
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        last_login DATETIME NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    """,

    # 房间表
    'rooms': """
    CREATE TABLE IF NOT EXISTS rooms (
        id INT AUTO_INCREMENT PRIMARY KEY,
        room_number VARCHAR(20) NOT NULL UNIQUE,
        name VARCHAR(100) NOT NULL,
        description TEXT NULL,
        room_type VARCHAR(20) NOT NULL,
        price DECIMAL(10,2) NOT NULL,
        size INT NULL,
        capacity INT NOT NULL,
        status VARCHAR(20) NOT NULL DEFAULT 'available',
        count INT NOT NULL DEFAULT 0,
        image_url VARCHAR(200) NULL,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    """,

    # 订单表
    'bookings': """
    CREATE TABLE IF NOT EXISTS bookings (
        id INT AUTO_INCREMENT PRIMARY KEY,
        guest_name VARCHAR(100) NOT NULL,
        guest_phone VARCHAR(20) NOT NULL,
        guest_email VARCHAR(120) NULL,
        room_id INT NOT NULL,
        check_in DATETIME NOT NULL,
        check_out DATETIME NOT NULL,
        total_price DECIMAL(10,2) NOT NULL,
        status VARCHAR(20) NOT NULL DEFAULT 'pending',
        notes TEXT NULL,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (room_id) REFERENCES rooms(id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    """,

    # 设施表
    'amenities': """
    CREATE TABLE IF NOT EXISTS amenities (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(50) NOT NULL,
        category VARCHAR(30) NOT NULL,
        icon VARCHAR(50) NULL,
        description TEXT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    """,

    # 房间-设施关联表
    'room_amenities': """
    CREATE TABLE IF NOT EXISTS room_amenities (
        room_id INT NOT NULL,
        amenity_id INT NOT NULL,
        PRIMARY KEY (room_id, amenity_id),
        FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE CASCADE,
        FOREIGN KEY (amenity_id) REFERENCES amenities(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    """,

    # 联系信息表
    'contacts': """
    CREATE TABLE IF NOT EXISTS contacts (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        email VARCHAR(120) NOT NULL,
        phone VARCHAR(20) NOT NULL,
        subject VARCHAR(100) NOT NULL,
        message TEXT NOT NULL,
        submitted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    """,

    # 客户表
    'customers': """
    CREATE TABLE IF NOT EXISTS customers (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        phone VARCHAR(20) NOT NULL UNIQUE,
        email VARCHAR(120) UNIQUE NULL,
        member_level VARCHAR(20) NOT NULL DEFAULT 'ordinary',
        total_stays INT NOT NULL DEFAULT 0,
        total_spent DECIMAL(12,2) NOT NULL DEFAULT 0,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    """
}


# ------------------------------
# 初始化数据库表
# ------------------------------
def init_db():
    """创建所有必要的数据库表"""
    conn = None
    cursor = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor()

        for table_name, sql in TABLE_DEFINITIONS.items():
            cursor.execute(sql)
            print(f"Table '{table_name}' initialized or already exists")

        conn.commit()
        return True
    except Exception as e:
        print(f"Error initializing database: {str(e)}")
        if conn:
            conn.rollback()
        return False
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()


# ------------------------------
# 房间相关操作
# ------------------------------
class Room:
    @staticmethod
    def get_by_id(room_id):
        """通过ID获取房间信息"""
        conn = None
        cursor = None
        try:
            conn = get_db_connection()
            cursor = conn.cursor(dictionary=True)
            cursor.execute("SELECT * FROM rooms WHERE id = %s", (room_id,))
            return cursor.fetchone()
        except Exception as e:
            print(f"Error getting room by ID: {str(e)}")
            return None
        finally:
            if cursor:
                cursor.close()
            if conn:
                conn.close()

    @staticmethod
    def get_all(filters=None, sort_by='popularity'):
        """获取所有房间，支持筛选和排序"""
        conn = None
        cursor = None
        try:
            conn = get_db_connection()
            cursor = conn.cursor(dictionary=True)

            query = "SELECT * FROM rooms WHERE 1=1"
            params = []

            # 处理筛选条件
            if filters:
                if 'room_type' in filters and filters['room_type'] != 'all':
                    query += " AND room_type = %s"
                    params.append(filters['room_type'])

                if 'capacity' in filters and filters['capacity'] != 'all':
                    if filters['capacity'] == '4':
                        query += " AND capacity >= 4"
                    else:
                        query += " AND capacity = %s"
                        params.append(filters['capacity'])

                if 'status' in filters:
                    query += " AND status = %s"
                    params.append(filters['status'])

            # 处理排序
            if sort_by == 'popularity':
                query += " ORDER BY count DESC"
            elif sort_by == 'price_asc':
                query += " ORDER BY price ASC"
            elif sort_by == 'price_desc':
                query += " ORDER BY price DESC"
            elif sort_by == 'newest':
                query += " ORDER BY created_at DESC"

            cursor.execute(query, params)
            return cursor.fetchall()
        except Exception as e:
            print(f"Error getting all rooms: {str(e)}")
            return []
        finally:
            if cursor:
                cursor.close()
            if conn:
                conn.close()

    @staticmethod
    def update_count(room_id, increment=1):
        """更新房间预订次数计数"""
        conn = None
        cursor = None
        try:
            conn = get_db_connection()
            cursor = conn.cursor()
            cursor.execute(
                "UPDATE rooms SET count = count + %s WHERE id = %s",
                (increment, room_id)
            )
            conn.commit()
            return True
        except Exception as e:
            print(f"Error updating room count: {str(e)}")
            if conn:
                conn.rollback()
            return False
        finally:
            if cursor:
                cursor.close()
            if conn:
                conn.close()


# ------------------------------
# 订单相关操作
# ------------------------------
class Order:
    @staticmethod
    def create(guest_name, guest_phone, room_id, check_in, check_out,
               total_price, guest_email=None, notes=None):
        """创建新订单"""
        conn = None
        cursor = None
        try:
            conn = get_db_connection()
            cursor = conn.cursor(dictionary=True)
            cursor.execute("""
                INSERT INTO bookings 
                (guest_name, guest_phone, guest_email, room_id, check_in, check_out, 
                 total_price, notes, status)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, 'pending')
            """, (guest_name, guest_phone, guest_email, room_id, check_in,
                  check_out, total_price, notes))
            conn.commit()
            return cursor.lastrowid  # 返回新创建的订单ID
        except Exception as e:
            print(f"Error creating order: {str(e)}")
            if conn:
                conn.rollback()
            return None
        finally:
            if cursor:
                cursor.close()
            if conn:
                conn.close()

    @staticmethod
    def get_by_room_and_dates(room_id, check_in, check_out):
        """检查指定日期范围内房间是否已被预订"""
        conn = None
        cursor = None
        try:
            conn = get_db_connection()
            cursor = conn.cursor(dictionary=True)
            cursor.execute("""
                SELECT * FROM bookings 
                WHERE room_id = %s 
                AND status != 'cancelled'
                AND (
                    (check_in < %s AND check_out > %s) OR
                    (check_in BETWEEN %s AND %s) OR
                    (check_out BETWEEN %s AND %s)
                )
            """, (room_id, check_out, check_in, check_in, check_out, check_in, check_out))
            return cursor.fetchall()
        except Exception as e:
            print(f"Error checking room availability: {str(e)}")
            return []
        finally:
            if cursor:
                cursor.close()
            if conn:
                conn.close()

    @staticmethod
    def get_recent_bookings(days=30, status=None):
        """获取最近指定天数的订单"""
        conn = None
        cursor = None
        try:
            conn = get_db_connection()
            cursor = conn.cursor(dictionary=True)

            query = """
                SELECT * FROM bookings 
                WHERE created_at >= %s
            """
            params = [(datetime.now() - timedelta(days=days)), ]

            if status:
                query += " AND status = %s"
                params.append(status)

            cursor.execute(query, params)
            return cursor.fetchall()
        except Exception as e:
            print(f"Error getting recent bookings: {str(e)}")
            return []
        finally:
            if cursor:
                cursor.close()
            if conn:
                conn.close()


# ------------------------------
# 设施相关操作
# ------------------------------
class Amenity:
    @staticmethod
    def get_by_room(room_id):
        """获取指定房间的所有设施"""
        conn = None
        cursor = None
        try:
            conn = get_db_connection()
            cursor = conn.cursor(dictionary=True)
            cursor.execute("""
                SELECT a.* FROM amenities a
                JOIN room_amenities ra ON a.id = ra.amenity_id
                WHERE ra.room_id = %s
                ORDER BY a.category, a.name
            """, (room_id,))
            return cursor.fetchall()
        except Exception as e:
            print(f"Error getting amenities for room: {str(e)}")
            return []
        finally:
            if cursor:
                cursor.close()
            if conn:
                conn.close()


# ------------------------------
# 用户相关操作
# ------------------------------
class User:
    @staticmethod
    def get_by_username(username):
        """通过用户名获取用户信息"""
        conn = None
        cursor = None
        try:
            conn = get_db_connection()
            cursor = conn.cursor(dictionary=True)
            cursor.execute("SELECT * FROM users WHERE username = %s", (username,))
            return cursor.fetchone()
        except Exception as e:
            print(f"Error getting user by username: {str(e)}")
            return None
        finally:
            if cursor:
                cursor.close()
            if conn:
                conn.close()

    @staticmethod
    def update_last_login(user_id):
        """更新用户最后登录时间"""
        conn = None
        cursor = None
        try:
            conn = get_db_connection()
            cursor = conn.cursor()
            cursor.execute(
                "UPDATE users SET last_login = %s WHERE id = %s",
                (datetime.now(), user_id)
            )
            conn.commit()
            return True
        except Exception as e:
            print(f"Error updating last login: {str(e)}")
            if conn:
                conn.rollback()
            return False
        finally:
            if cursor:
                cursor.close()
            if conn:
                conn.close()


# ------------------------------
# 联系人相关操作
# ------------------------------
class Contact:
    @staticmethod
    def create(name, email, phone, subject, message):
        """创建新的联系信息记录"""
        conn = None
        cursor = None
        try:
            conn = get_db_connection()
            cursor = conn.cursor()
            cursor.execute("""
                INSERT INTO contacts (name, email, phone, subject, message)
                VALUES (%s, %s, %s, %s, %s)
            """, (name, email, phone, subject, message))
            conn.commit()
            return True
        except Exception as e:
            print(f"Error creating contact: {str(e)}")
            if conn:
                conn.rollback()
            return False
        finally:
            if cursor:
                cursor.close()
            if conn:
                conn.close()
